package net.biz.reminder.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class JDBCOracleUtil {
	// 日志记录标志
	private static boolean LOG_ENABLE = false;

	/**
	 * 获取连接
	 * 
	 * @param user
	 * @param password
	 * @param host
	 * @param sid
	 * @param port
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection getConnection(String user, String password,
			String host, String sid, String port)
			throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@".concat(host).concat(":").concat(port)
				.concat(":").concat(sid);

		Class.forName(driver).newInstance();
		Connection conn = DriverManager.getConnection(url, user, password);

		conn.setAutoCommit(false);

		return conn;
	}

	/**
	 * 根据properties文件获取连接
	 * 
	 * @return
	 * @throws IOException
	 * @throws FileNotFoundException
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	public static Connection getConnection() throws FileNotFoundException,
			IOException, InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Properties prop = new Properties();
		prop.load(new FileInputStream("reminder.properties"));
		return getConnection(prop.getProperty("db.user"),
				prop.getProperty("db.password"), prop.getProperty("db.host"),
				prop.getProperty("db.sid"), prop.getProperty("db.port"));
	}

	/**
	 * 获取连接
	 * 
	 * @param user
	 * @param password
	 * @param url
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection getConnection(String user, String password,
			String url) throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		String driver = "oracle.jdbc.driver.OracleDriver";
		Class.forName(driver).newInstance();
		Connection conn = DriverManager.getConnection(url, user, password);

		conn.setAutoCommit(false);

		return conn;
	}

	/**
	 * 关闭连接，建议在finally中使�? *
	 * 
	 * @param conn
	 * @throws SQLException
	 */
	public static void closeConnection(Connection conn) throws SQLException {
		if (conn != null) {
			conn.rollback();
			conn.close();
		}
	}

	/**
	 * 调用存储过程
	 * 
	 * @param inList
	 * 
	 * @param outList
	 * 
	 * @param procName
	 * 
	 * @param conn
	 *            jdbcl
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public static List<String> callProc(List inList, List outList,
			String procName, Connection conn) throws SQLException {
		String sql = String.valueOf("");
		String in = String.valueOf("");
		String out = String.valueOf("");
		List<String> retList = new ArrayList<String>();

		int n = 0;
		n = inList.size();

		conn.setAutoCommit(false);

		if (inList.size() > 0) {
			for (int i = 0; i < inList.size(); i++) {
				in = in + "?,";
			}
		}
		if (outList.size() > 0) {
			n = inList.size();
			for (int i = 0; i < outList.size(); i++) {
				out = out + "?,";
			}
		}
		if (in.length() > 0) {
			in = (in.concat(out)).substring(0, in.length() + out.length() - 1);
		}
		sql = "{call " + procName + "(" + in + ")}";
		CallableStatement cstat = conn.prepareCall(sql);
		if (inList.size() > 0) {
			for (int i = 0; i < inList.size(); i++) {
				cstat.setObject(i + 1, inList.get(i));
			}
		}
		if (outList.size() > 0) {

			for (int i = n + 1; i < n + 1 + outList.size(); i++) {
				cstat.registerOutParameter(i, OracleTypes.VARCHAR);
			}
		}
		// TODO: 替换
		System.out.println("调用过程过程:" + procName);
		System.out.println("输入参数:" + inList);
		cstat.executeUpdate();
		OracleCallableStatement ocstat = (OracleCallableStatement) cstat;

		for (int i = 0; i < outList.size(); i++) {
			retList.add(i, ocstat.getString(i + n + 1));
		}

		cstat.close();
		ocstat.close();
		System.out.println(sql);
		System.out.println("入参:" + inList);
		System.out.println("结果:" + retList);
		return retList;

	}

	/**
	 * 使用非dml语句 不设置是否自动提�?如果结果是ORA-00942，没有表或视图，则跳�? *
	 * 
	 * @param dml
	 * @param conn
	 * @throws Exception
	 * @throws BIException
	 */
	public static void executeDML(String dml, Connection conn) throws Exception {

		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(dml);

		} catch (SQLException e) {
			String m = e.getMessage();
			if (m.indexOf("RA-00942") > 0) {

			} else {
				throw new Exception(m);
			}
		} finally {
			stmt.close();
		}
	}

	/**
	 * 查询结果，不使用prepare的方�?�?�?�� 如果是null，设置为空字�? *
	 * 
	 * @param sql
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> executeQuery(String sql,
			Connection conn) throws SQLException {
		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		try {
			System.out.println("执行sql:" + sql.toUpperCase());
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					row.put(rsmd.getColumnName(i), rs.getObject(i) == null ? ""
							: rs.getObject(i));
				}
				data.add(row);
			}
		} finally {
			rs.close();
			stmt.close();
			conn.close();
		}
		return data;
	}

	/**
	 * 执行查询
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws FileNotFoundException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public static List<Map<String, Object>> executeQuery(String sql)
			throws SQLException, FileNotFoundException, IOException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		return executeQuery(sql, getConnection());
	}

	/**
	 * 查询结果，不使用prepare的方法 如果是null，设置为空字�? *
	 * 
	 * @param sql
	 * @param conn
	 * @param 绑定变量
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> executeQuery(String sql,
			Connection conn, List<Object> param) throws SQLException {
		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
		PreparedStatement stmt = conn.prepareStatement(sql);
		for (int i = 0; i < param.size(); i++) {
			stmt.setObject(i + 1, param.get(i));
		}
		ResultSet rs = stmt.executeQuery();
		try {
			System.out.println("执行sql:" + sql.toUpperCase());
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					row.put(rsmd.getColumnName(i), rs.getObject(i) == null ? ""
							: rs.getObject(i));
				}
				data.add(row);
			}
		} finally {
			rs.close();
			stmt.close();
			conn.close();
		}
		return data;
	}

	/**
	 * 执行查询SQL
	 * 
	 * @param sql
	 * @param param
	 * @return
	 * @throws SQLException
	 * @throws FileNotFoundException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public static List<Map<String, Object>> executeQuery(String sql,
			List<Object> param) throws SQLException, FileNotFoundException,
			IOException, InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		return executeQuery(sql, getConnection(), param);
	}

	/**
	 * 调用标准存储过程
	 * 
	 * 输入参数若干 输出参数2�?�?��number,�?��varchar2
	 * 
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public static void callProcduce(List inList, List<Object> outList,
			String procName, Connection conn) throws SQLException {
		String sql = String.valueOf("");
		String in = String.valueOf("");
		int n = 0;
		n = inList.size();

		conn.setAutoCommit(false);

		// 计算存储过程的参数个数（输入参数个数+2），用?表示
		if (inList.size() > 0) {
			for (int i = 0; i < inList.size() + 2; i++) {
				in = in + "?,";
			}
			in = in.substring(0, in.length() - 1);// 去掉in后面最后的逗号
		} else// 没有输入参数，只有两个输出参数
		{
			in = "?,?";
		}

		sql = "{call " + procName + "(" + in + ")}";
		CallableStatement cstat = conn.prepareCall(sql);
		if (inList.size() > 0) {
			for (int i = 0; i < inList.size(); i++) {
				cstat.setObject(i + 1, inList.get(i));
			}
		}
		// 注册两个输出，存储过程的输出
		cstat.registerOutParameter(inList.size() + 1, OracleTypes.NUMBER);
		cstat.registerOutParameter(inList.size() + 2, OracleTypes.VARCHAR);

		cstat.executeUpdate();
		// OracleCallableStatement ocstat = (OracleCallableStatement) cstat;

		// 获取两个输出内容
		for (int i = 0; i < 2; i++) {
			outList.add(i, cstat.getObject(i + n + 1));
		}
		cstat.close();
		// ocstat.close();
		System.out.println(sql);
		System.out.println("入参:" + inList);
		System.out.println("结果:" + outList);

	}

	/**
	 * 执行存储过程
	 * 
	 * @param inList
	 * @param outList
	 * @param procName
	 * @throws SQLException
	 * @throws FileNotFoundException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public static void callProcduce(List inList, List<Object> outList,
			String procName) throws SQLException, FileNotFoundException,
			IOException, InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		Connection conn = getConnection();
		callProcduce(inList, outList, procName, conn);
		conn.close();
	}

	/**
	 * 绑定变量方式执行SQL，用于外部统�?��制事务，conn应在外面提交、回滚和关闭
	 * 
	 * @param sql
	 * @param params
	 * @param conn
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public static void executeDML(String sql, Object[] params, Connection conn)
			throws SQLException {
		PreparedStatement st = null;
		try {
			st = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				st.setObject(i + 1, params[i]);
			}
			st.execute();
		} finally {
			if (st != null)
				st.close();
		}
	}

	/**
	 * 绑定变量方式执行SQL，外部控制连接打�??关闭、提�? *
	 * 
	 * @param sql
	 * @param params
	 * @param conn
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public static void ExecuteDML(String sql, List<Object> params,
			Connection conn) throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {
		Object[] param = new Object[params.size()];
		for (int i = 0; i < params.size(); i++) {
			param[i] = params.get(i);
		}
		executeDML(sql, param, conn);
	}

}
